package nz.ac.massey.webtech.servlets.xml;

import org.jdom.*;
import java.sql.*;
/**
 * Utility class that converts the results of a query to a document.
 * @author  Jens Dietrich   
 * @version 1.0
 */
public class SQL2XML {
    /** Creates a new instance of SQL2XML */
    public SQL2XML() {
    }
    // get the sql statement 
    public static String getSql() {
        return "SELECT * FROM CATALOG";
    }
    // get the database driver name 
    public static String getDriver() {
        return "com.mysql.jdbc.Driver";
    }
    // get the database url
    public static String getUrl() {
        return "jdbc:mysql://localhost/test";
    }
    // get the database user name
    public static String getUser() {
        return null;
    }
        // get the database password
    public static String getPassword() {
        return null;
    }
    /**
     * Issue the query and return the results converted into a document.
     */ 
    public Document query() throws Exception {
        // prepare database connection
        Class.forName(getDriver());
        Connection con = DriverManager.getConnection(getUrl(),getUser(),getPassword());
        Statement stmnt = con.createStatement();
        
        // create xml document 
        Element root = new Element("catalog");
        Document doc = new Document(root);
        
        // issue query (SELECT)
        ResultSet rs = stmnt.executeQuery(getSql());
        while (rs.next()) {
            // add an element to xml for each row
            Element cd = new Element("cd"); 
            root.addContent(cd);
            
            Element e = new Element("title");
            e.addContent(rs.getString("title"));
            cd.addContent(e);

            e = new Element("artist");
            e.addContent(rs.getString("artist"));
            cd.addContent(e);
            
            e = new Element("country");
            e.addContent(rs.getString("country"));
            cd.addContent(e);
            
            e = new Element("company");
            e.addContent(rs.getString("company"));
            cd.addContent(e);
            
            e = new Element("price");
            e.addContent(rs.getString("price"));
            cd.addContent(e);
            
            e = new Element("year");
            e.addContent(rs.getString("year"));
            cd.addContent(e);
        }
        con.close();
        return doc;
    }
}
